Database Tutorials Full-Text Search Index তৈরি করা (GIN এবং GIST Index) গাইড ও নোট

PostgreSQL-এ Full-Text Search (FTS) ডেটাবেসে শব্দ অনুসন্ধানের জন্য একটি শক্তিশালী বৈশিষ্ট্য। এটি সাধারণত বড় টেক্সট ডেটা সেটে দ্রুত অনুসন্ধান সক্ষম করতে ব্যবহৃত হয়। PostgreSQL FTS-এ GIN (Generalized Inverted Index) এবং GIST (Generalized Search Tree) ইনডেক্স দুটি প্রধান ধরনের ইনডেক্স হিসেবে ব্যবহৃত হয়।

1. Full-Text Search (FTS) Indexing Overview

PostgreSQL-এর Full-Text Search প্রক্রিয়া ডেটাবেসে শব্দ এবং শব্দের গঠন অনুসন্ধান করার জন্য ব্যবহৃত হয়। সাধারণত, টেক্সটের উপর একটি tsvector এবং tsquery ব্যবহার করা হয়।

• tsvector: এটি একটি বিশেষ টেক্সট ডেটা টাইপ, যা টেক্সট ডকুমেন্টের শব্দের একটি তালিকা তৈরি করে এবং ইনডেক্সযোগ্য তৈরি করে।
• tsquery: এটি ব্যবহারকারী থেকে প্রাপ্ত অনুসন্ধান প্রশ্ন (search query) যা শব্দ অনুসন্ধানের জন্য ব্যবহৃত হয়।

Full-Text Search Indexing Setup Example:

1. একটি টেবিল তৈরি করা: ধরুন, আমাদের একটি টেবিল আছে যেখানে টেক্সট ডেটা সঞ্চিত থাকে এবং আমরা তার উপর ফুল-টেক্সট অনুসন্ধান করতে চাই।

   CREATE TABLE documents (
       id SERIAL PRIMARY KEY,
       title TEXT,
       body TEXT
   );
   

2. Text Search Vector Column তৈরি করা: টেক্সট অনুসন্ধান করার জন্য, প্রথমে আপনাকে টেক্সটের ভেক্টর (tsvector) তৈরি করতে হবে।

   ALTER TABLE documents ADD COLUMN document_vector tsvector;
   

3. tsvector এ ডেটা ভ্যালু ইনসার্ট করা: এখানে, আমরা title এবং body কলামের উপর ভিত্তি করে tsvector তৈরি করব।

   UPDATE documents
   SET document_vector = to_tsvector('english', title || ' ' || body);
   

   এখানে, to_tsvector('english', ...) শব্দগুলি ইংরেজি ভাষায় প্রক্রিয়া করে, এবং || দিয়ে টাইটেল এবং বডি একত্রিত করা হচ্ছে।

4. GIN বা GIST ইনডেক্স তৈরি করা: এরপর, ফুল-টেক্সট অনুসন্ধান দ্রুততর করার জন্য, আমরা GIN বা GIST ইনডেক্স তৈরি করতে পারি।

2. GIN (Generalized Inverted Index)

GIN (Generalized Inverted Index) সাধারণত Full-Text Search এবং JSONB ডেটা টাইপের জন্য ব্যবহৃত হয়। এটি শব্দের তালিকাতে দ্রুত অনুসন্ধান করতে সক্ষম এবং এটি অনেক বেশি ডেটাতে কার্যকরী, কারণ এটি একটি "inverted index" তৈরি করে যেখানে শব্দের সাথে সম্পর্কিত রেকর্ডগুলির তালিকা রাখা হয়।

GIN ইনডেক্স তৈরির উদাহরণ

CREATE INDEX document_vector_gin_idx
ON documents USING gin (document_vector);

এটি document_vector কলামের উপর একটি GIN ইনডেক্স তৈরি করবে, যা ফুল-টেক্সট অনুসন্ধান দ্রুততর করবে।

GIN এর সুবিধা:

• দ্রুত অনুসন্ধান: বড় ডেটা সেটে দ্রুত শব্দ অনুসন্ধান সম্ভব।
• উচ্চ পারফরম্যান্স: খুব বেশি ডেটার মধ্যে ফাস্ট টেক্সট অনুসন্ধান নিশ্চিত করে।

3. GIST (Generalized Search Tree)

GIST (Generalized Search Tree) একটি আরো সাধারণ উদ্দেশ্যের জন্য ব্যবহৃত ইনডেক্স, যা ডেটার মধ্যে বিভিন্ন অনুসন্ধান অপারেশন (যেমন স্পেসিয়াল, ফিল্টারিং) সমর্থন করে। GIST ইনডেক্স সাধারণত জিওস্পেশিয়াল ডেটা বা আরও কাস্টম অনুসন্ধানগুলির জন্য ব্যবহার করা হয়, তবে ফুল-টেক্সট অনুসন্ধানে GIN এর তুলনায় কিছুটা কম কার্যকরী হতে পারে।

GIST ইনডেক্স তৈরির উদাহরণ

CREATE INDEX document_vector_gist_idx
ON documents USING gist (document_vector);

এটি document_vector কলামের উপর একটি GIST ইনডেক্স তৈরি করবে।

GIST এর সুবিধা:

• বৈচিত্র্যপূর্ণ ব্যবহৃত ক্ষেত্র: GIST ইনডেক্স বিভিন্ন ধরনের ডেটার জন্য ব্যবহৃত হতে পারে, যেমন স্পেসিয়াল ডেটা, কাস্টম টাইপ ইত্যাদি।
• টেক্সট অনুসন্ধানে ব্যবহার: যদিও এটি GIN এর তুলনায় কিছুটা কম কার্যকরী, তবে কিছু ক্ষেত্রে GIST ফাস্ট হতে পারে, বিশেষ করে যদি বিশেষ ধরনের টেক্সট অনুসন্ধান করতে হয়।

4. Full-Text Search Query Example

একবার ইনডেক্স তৈরি করার পরে, আপনি ফুল-টেক্সট অনুসন্ধান করতে পারেন tsquery এবং tsvector ব্যবহার করে।

Simple Full-Text Search Query

এখন, আপনি ব্যবহারকারীর একটি অনুসন্ধান প্রশ্ন ব্যবহার করে ডেটাবেসে অনুসন্ধান করতে পারেন।

SELECT id, title, body
FROM documents
WHERE document_vector @@ to_tsquery('english', 'database & search');

এটি "database" এবং "search" শব্দের মধ্যে যেকোনো রেকর্ড খুঁজে বের করবে।

Phrase Search (যতটা শব্দ কাছাকাছি থাকে)

এছাড়া, আপনি phrase search করতে পারেন যেখানে শব্দ দুটি কাছাকাছি থাকতে হবে:

SELECT id, title, body
FROM documents
WHERE document_vector @@ phraseto_tsquery('english', 'full text search');

এটি "full text search" শব্দের মধ্যে একটি প্যারাগ্রাফ বা বাক্য অনুসন্ধান করবে যেখানে দুটি শব্দ একে অপরের কাছে থাকবে।

5. Performance Considerations

• GIN Index vs GIST Index:
  • GIN ইনডেক্স ফাস্ট এবং খুব বড় ডেটাসেটের জন্য উপযুক্ত। এটি টেক্সট অনুসন্ধানের জন্য অধিকাংশ সময় বেশি কার্যকরী।
  • GIST ইনডেক্স সাধারণত কম কার্যকরী, তবে এটি অন্য ধরনের ডেটার জন্য ব্যবহার করা যেতে পারে, যেমন স্পেসিয়াল ডেটা বা কাস্টম টেবিল।
• Index Size:
  • GIN ইনডেক্সগুলি তুলনামূলকভাবে বড় হতে পারে, তবে তারা বেশি কার্যকরী ডেটাসেটের জন্য।
  • GIST ইনডেক্সগুলি তুলনামূলকভাবে ছোট এবং দ্রুত তৈরি করা যায়, তবে তারা কিছু ক্ষেত্রে GIN এর মতো কার্যকরী নয়।
• Index Maintenance:
  • GIN ইনডেক্সে ডেটা পরিবর্তনের সময় (inserts, updates) একটু সময় বেশি লাগে।
  • GIST ইনডেক্সে এই সমস্যা তুলনামূলকভাবে কম হতে পারে।

সারাংশ

Full-Text Search Indexing PostgreSQL-এ খুবই শক্তিশালী এবং GIN এবং GIST ইনডেক্স ব্যবহার করে দ্রুত টেক্সট অনুসন্ধান নিশ্চিত করা যায়। GIN ইনডেক্স সাধারণত Full-Text Search এর জন্য অধিক কার্যকরী এবং গতি প্রদান করে, তবে GIST ইনডেক্স কিছু ক্ষেত্রে ব্যবহার করা যেতে পারে, বিশেষ করে যদি স্পেসিয়াল ডেটার জন্য ইনডেক্স তৈরি করতে হয়।

FTS Indexing আপনার PostgreSQL ডেটাবেসের অনুসন্ধান দক্ষতা ব্যাপকভাবে উন্নত করে এবং আপনি এই টুলগুলি ব্যবহার করে দ্রুত এবং দক্ষ অনুসন্ধান করতে পারবেন।